package com.weavernorth.retailExperienceAward.action;

import com.weavernorth.retailExperienceAward.utils.sendDataToSapUtils;
import com.weavernorth.retailExperienceAward.dto.sapMessEntity;
import com.weavernorth.retailExperienceAward.utils.commonsUtil;
import com.weavernorth.sysap.util.SyncUtil;
import com.weavernorth.util.Logger4jUtil;
import com.weavernorth.util.dateUtils;
import org.apache.log4j.Logger;
import weaver.conn.ConnStatement;
import weaver.conn.RecordSet;
import weaver.conn.RecordSetTrans;
import weaver.general.Util;
import weaver.interfaces.workflow.action.Action;
import weaver.soa.workflow.request.RequestInfo;
import java.util.List;
import static com.weavernorth.retailExperienceAward.utils.commonsUtil.doubleFormat;
import static com.weavernorth.util.dateUtils.getLastDate;

/**
 * @Author: bruseewang
 * @Package: com.weavernorth.retailExperienceAward.action
 * @date: 2020/6/15 18:07
 * @description: 终端店铺分数统计推送至SAP(区域经理)
 */
public class qyjlRetailAwardToSAP implements Action {
    private ConnStatement statement;
    private RecordSetTrans rsTrans;

    @Override
    public String execute(RequestInfo requestInfo) {
        Logger log = Logger4jUtil.getLoggerByName("/action/qyjlRetailAwardToSAP/");
        RecordSet rs = null;
        RecordSet rs_insertData = new RecordSet();
        RecordSet rs_select = new RecordSet();
        RecordSet rsUpdate = new RecordSet();
        log.info("区域经理零售体验奖传SAP数据开始。。。");
        //请求id
        String strRequestid = requestInfo.getRequestid();
        log.info("流程requestid=" + strRequestid);
        String strTable = requestInfo.getRequestManager().getBillTableName();
        log.info("表名：" + strTable);
        String strKhzq = "";
        //查询表单数据
        log.info("获取表单所有数据开始......");
        rs = getAllData(strTable, strRequestid,log);
        log.info("获取表单所有数据结束......");
        //报文请求体
        StringBuffer requestXml;
        requestXml = new StringBuffer();
        String str_jjjs=null;
        String str_jjjs_gr=null;
        String str_jjjs_gs=null;
        Double db_jjje=0.00;
        Double str_qjDays=0.00;
        //拼接请求体
        requestXml.append("<soapenv:Envelope xmlns:soapenv='http://schemas.xmlsoap.org/soap/envelope/' xmlns:urn='urn:sap-com:document:sap:rfc:functions'>" +
                "<soapenv:Header/>" + "<soapenv:Body>" + "<urn:ZHRI_075>" + " <RETURN>" +
                "<!--Zero or more repetitions:-->" +
                "<item>" +
                "<TYPE></TYPE>" +
                "<ID></ID>" +
                "<NUMBER></NUMBER>" +
                "<MESSAGE></MESSAGE>" +
                "<LOG_NO></LOG_NO>" +
                "<LOG_MSG_NO></LOG_MSG_NO>" +
                "<MESSAGE_V1></MESSAGE_V1>" +
                "<MESSAGE_V2></MESSAGE_V2>" +
                "<MESSAGE_V3></MESSAGE_V3>" +
                "<MESSAGE_V4></MESSAGE_V4>" +
                "<PARAMETER></PARAMETER>" +
                "<ROW></ROW>" +
                "<FIELD></FIELD>" +
                "<SYSTEM></SYSTEM>" +
                "</item>" +
                "</RETURN>" +
                "<T_9133>" +
                "<!--Zero or more repetitions:-->");
        while (rs.next()){
            strKhzq=Util.null2String(rs.getString("khzq"));
            //获取员工编号
            String strFgs = Util.null2String(rs.getString("fgs"));
            //获取员工编号
            String strYgbh = Util.null2String(rs.getString("gh"));
            log.info("员工编号：" + strYgbh);
            //拼接XML
            requestXml.append(" <item>" + "<PERNR>" + strYgbh + "</PERNR>");
            //获取CRM评分
            String strCRM = Util.null2String(rs.getString("crm5"));
            log.info("CRM评分：" + strCRM);
            //拼接XML
            requestXml.append("<ZCRMPF>" + strCRM + "</ZCRMPF>");
            //获取零售运营评分
            String strLsyy = Util.null2String(rs.getString("yybz35"));
            log.info("零售运营评分：" + strLsyy);
            //拼接XML
            requestXml.append("<ZLSYYPF>" + strLsyy + "</ZLSYYPF>");
            //获取服务标准评分
            String strFwbz = Util.null2String(rs.getString("fwbz40"));
            log.info("服务标准评分：" + strFwbz);
            //拼接XML
            requestXml.append("<ZFWBZPF>" + strFwbz + "</ZFWBZPF>");
            //获取陈列管理
            String strClgl = Util.null2String(rs.getString("clbz20"));
            log.info("陈列管理评分：" + strClgl);
            //拼接XML
            requestXml.append("<ZCLGLPF>" + strClgl + "</ZCLGLPF>");
            //总得分
            String strZdf = Util.null2String(rs.getString("zdf"));
            log.info("总得分：" + strZdf);
            //拼接XML
            requestXml.append("<ZDF>" + strZdf + "</ZDF>");
            //评分日期
            String strPfrq = Util.null2String(rs.getString("khrq"));
            log.info("评分日期：" + strPfrq);
            log.info("传至SAP日期：" + dateUtils.getLastDate(strPfrq));
            //拼接XML
            requestXml.append("<ZSP_DATE>" + getLastDate(strPfrq) + "</ZSP_DATE>");
            //姓名
            String strXm=Util.null2String(rs.getString("xm"));
            log.info("员工id:" + strXm);
            String strGzfw= sendDataToSapUtils.getGzfwFromUser(strXm);
            log.info("工资核算范围:" + strGzfw);
            String strLockMess = sendDataToSapUtils.judgeLock(log,strGzfw, strPfrq);
            log.info("工资核算范围返回信息:" + strLockMess);
            if (!"".equals(strLockMess)) {
                strLockMess="工号为:"+strYgbh+","+strLockMess+"薪资核算结束后，工资范围解除锁定，流程可以继续提交";
                log.info(strLockMess);
            }
            //个人奖金基数
            str_jjjs_gr=Util.null2String(rs.getString("jjjs_gr"));
            log.info("个人奖金基数:" + str_jjjs_gr);
            //分公司奖金基数
            str_jjjs_gs=Util.null2String(rs.getString("jjjs_gs"));
            log.info("分公司奖金基数:" + str_jjjs_gs);
            if (!"".equals(str_jjjs_gr)&&str_jjjs_gr !=null){
                str_jjjs=str_jjjs_gr;
            }else{
                str_jjjs=str_jjjs_gs;
            }
            //总得分
            Double db_zdf=Util.getDoubleValue(strZdf,0.00);
            log.info("总得分计算前：" + db_zdf);
            //请假天数
            str_qjDays=Util.getDoubleValue(rs.getString("qjts"),0.00);
            log.info("请假天数：" + str_qjDays);
            if (str_jjjs!=null && !"".equals(str_jjjs) && db_zdf!=0.00){
                db_jjje=(db_zdf/100)*Util.getDoubleValue(str_jjjs,0.00);
                //根据请假天数调整奖金金额
                db_jjje= doubleFormat(commonsUtil.convertAmount(db_jjje,str_qjDays));
                log.info("奖金金额：" + db_jjje);
            }
            //拼接XML
            requestXml.append("<ZJE>" + db_jjje + "</ZJE>" + "</item>");
        }
        requestXml.append("</T_9133>" +
                "</urn:ZHRI_075>" +
                "</soapenv:Body>" +
                "</soapenv:Envelope>");
        List<sapMessEntity> list = sendDataToSapUtils.toSapMethod(log,requestXml.toString());
        String strAllFlag="";
        String strMessage="";
        String strGh="";
        for (sapMessEntity entity : list) {
            if (entity.getType().equals("allS")){
                strAllFlag="allS";//全部成功
                strMessage=entity.getMessage();
            }else if (entity.getType().equals("allE")){
                strAllFlag="allE";//全部失败
                strMessage=entity.getMessage();
            }else{
                strAllFlag="E";//部分成功部分失败
            }
        }
        if (list.size()<1) {
            log.info("接口信息解析异常！");
            requestInfo.getRequestManager().setMessagecontent("接口信息解析异常!");
            return FAILURE_AND_CONTINUE;
        }else if ("allE".equals(strAllFlag)){//全部失败
            log.info("SAP接口传数失败,失败原因："+strMessage);
            requestInfo.getRequestManager().setMessagecontent(strMessage);
            return FAILURE_AND_CONTINUE;
        } else  if ("allS".equals(strAllFlag)) {//全部成功
            log.info("全部成功！");
            try {
                Boolean getResult = InserDataAll(strTable, strRequestid,log);
                if (!getResult) {
                    requestInfo.getRequestManager().setMessagecontent("批量插入数据失败,请查看日志！");
                    return FAILURE_AND_CONTINUE;
                }
            } catch (Exception ex) {
                log.info("插入数据发生异常：" + ex.toString());
                requestInfo.getRequestManager().setMessagecontent("插入数据发生异常:" + ex.toString());
                return FAILURE_AND_CONTINUE;
            }

        }
        else{//部分成功部分失败
            log.info("接口返回信息："+list.toString());
            for (sapMessEntity entity : list) {
                if (!entity.getType().equals("allS")&&!entity.getType().equals("allE")){
                    if (entity.getType().equals("E")){
                        strMessage+=entity.getMessage()+";";
                    }else{
                        strGh=entity.getYgbh();
                        if (!"".equals(strGh)){
                            if (existsData(strKhzq,strGh,log)){//如果存在就更新
                                log.info("已存在的数据则更新,考核周期："+strKhzq+";工号："+strGh);
                                Boolean up_result= UpdateData(strTable,strKhzq,strRequestid,strGh,log);
                                if (!up_result){
                                    strMessage="工号为："+strGh+"的数据更新失败！";
                                    log.info("数据更新失败！");
                                    break;
                                }
                            }else{//插入数据
                                log.info("不存在的数据则插入,员工编号："+strGh);
                                InserData(strTable,strRequestid,strGh,log);

                            }
                        }
                    }
                }
            }
            requestInfo.getRequestManager().setMessagecontent(strMessage);
            return FAILURE_AND_CONTINUE;
        }
        return SUCCESS;
    }

    /***
     * 判断数据是否存在,存在返回true,否则返回false
     * @param strKhzq
     * @param strGh
     * @return
     */
    public Boolean existsData(String strKhzq, String strGh,Logger log) {
        RecordSet rs = new RecordSet();
        String strsql = "select 1 from uf_zddpgrjj_qyjl where  khzq='" + strKhzq + "' and gh='" + strGh + "'";
        log.info("根据员工编号和requestid查询是否存在数据Sql："+strsql);
        rs.executeQuery(strsql);
        int result=rs.getCounts();
        log.info("受影响行数："+result);
        if (result>0) {
            return true;
        }
        return false;
    }

    public Boolean InserDataAll(String strTable, String strRequestid,Logger log) {
        RecordSet rsinsert = new RecordSet();
        Boolean isExists = true;
        int counts = 0;
        try {
            log.info("批量插入数据...");
            RecordSet rs = getAllData(strTable, strRequestid,log);
            log.info("表名：" + strTable + ";requestid:" + strRequestid);
            counts = rs.getCounts();
            log.info("结果集行数：" + isExists);
            if (counts > 0) {
                isExists = true;
            } else {
                isExists = false;
            }
            String str_jjjs = null;
            String str_jjjs_gr = null;
            String str_jjjs_gs = null;
            Double db_jjje = 0.00;
            Double str_qjDays = 0.00;
            while (rs.next()) {
                //获取分公司
                String strfgs = Util.null2String(rs.getString("fgs"));
                log.info("分公司：" + strfgs);
                //获取员工id
                String strxm = Util.null2String(rs.getString("xm"));
                log.info("员工id：" + strxm);
                //获取员工编号
                String strYgbh = Util.null2String(rs.getString("gh"));
                log.info("员工编号：" + strYgbh);
                //职务
                String strZw = Util.null2String(rs.getString("zw"));
                log.info("职务：" + strYgbh);

                //获取CRM评分
                Double crm5 =  Util.getDoubleValue(rs.getString("crm5"),0.00);
                log.info("CRM评分：" + crm5);

                //获取零售运营评分
                Double yybz35 = Util.getDoubleValue(rs.getString("yybz35"),0.00);
                log.info("零售运营评分：" + yybz35);

                //获取服务标准评分
                Double fwbz40 = Util.getDoubleValue(rs.getString("fwbz40"),0.00);
                log.info("服务标准评分：" + fwbz40);

                //获取陈列管理
                Double clbz20 = Util.getDoubleValue(rs.getString("clbz20"),0.00);
                log.info("陈列管理：" + clbz20);
                //总得分
                String strZdf = Util.null2String(rs.getString("zdf"));
                log.info("总得分：" + strZdf);
                //评分日期
                String khrq = Util.null2String(rs.getString("khrq"));
                log.info("评分日期：" + khrq);
                //考核周期
                String khzq = Util.null2String(rs.getString("khzq"));
                log.info("考核周期：" + khzq);
                //个人奖金基数
                str_jjjs_gr = Util.null2String(rs.getString("jjjs_gr"));
                log.info("个人奖金基数：" + str_jjjs_gr);
                //分公司奖金基数
                str_jjjs_gs = Util.null2String(rs.getString("jjjs_gs"));
                log.info("分公司奖金基数：" + str_jjjs_gs);
                if (!"".equals(str_jjjs_gr) && str_jjjs_gr != null) {
                    str_jjjs = str_jjjs_gr;
                } else {
                    str_jjjs = str_jjjs_gs;
                }
                //总得分
                Double db_zdf = Util.getDoubleValue(strZdf, 0.00);
                //请假天数
                str_qjDays = Util.getDoubleValue(rs.getString("qjts"), 0.00);
                log.info("请假天数：" + str_qjDays);
                if (str_jjjs != null && !"".equals(str_jjjs) && db_zdf != 0.00) {
                    db_jjje = (db_zdf / 100) * Util.getDoubleValue(str_jjjs, 0.00);
                    //根据请假天数调整奖金金额
                    db_jjje = doubleFormat(commonsUtil.convertAmount(db_jjje, str_qjDays));
                    log.info("根据请假天数调整奖金金额：" + db_jjje);
                }
                try{
                    SyncUtil syncUtil = new SyncUtil();
                    String strFormid = syncUtil.getModeIdBytableName("uf_zddpgrjj_qyjl");
                    String strInsertData = "insert into uf_zddpgrjj_qyjl " +
                            "(fgs,xm,gh,zw,fwbz40,clbz20,crm5,yybz35,zdf,khzq,khrq,formmodeid,modedatacreater,modedatacreatertype,modedatacreatedate,modedatacreatetime,tsjg,qjts,jjje) " +
                            "values(" + strfgs + "," + strxm + ",'" + strYgbh + "'," + strZw + "," + fwbz40 + "," + clbz20 + " ," + crm5 + " ," + yybz35 + "," + db_zdf + ",'" + khzq + "','" + khrq + "',"+strFormid+",1,0,to_char(SYSDATE,'yyyy-mm-dd'),to_char(SYSDATE,'hh24:mi:ss'),0," + str_qjDays + "," + db_jjje + ")";
                    log.info("终端店铺个人统计-区域经理插入数据sql：" + strInsertData);
                    Boolean insertResult = rsinsert.executeUpdate(strInsertData);
                    if (insertResult) {
                        log.info("数据插入到个人统计表成功！");
                    }
                    else{
                        log.info("数据插入到个人统计表失败！");
                        isExists = false;
                    }
                }catch (Exception ex){
                    log.info("插入数据异常,异常信息："+ex.toString());
                    isExists = false;
                }
            }
        } catch (Exception ex) {
            log.info("数据插入到个人统计表发生异常：" + ex.toString());
            isExists = false;
        }
        return isExists;
    }

    public void InserData(String strTable, String strRequestid, String strGh,Logger log) {
        RecordSet rsinsert = new RecordSet();
        String str_jjjs = "0.00";
        String str_jjjs_gr = "";
        String str_jjjs_gs = "";
        Double db_jjje = 0.00;
        Double str_qjDays = 0.00;
        try {
            log.info("strTable:"+strTable+";strRequestid:"+strRequestid+";strGh:"+strGh);
            RecordSet rs = getAllData(strTable, strRequestid,log);
            while (rs.next()) {
                //获取分公司
                String strfgs = Util.null2String(rs.getString("fgs"));
                log.info("分公司："+strfgs);
                //获取员工id
                String strxm = Util.null2String(rs.getString("xm"));
                log.info("员工id："+strxm);
                //获取员工编号
                String strYgbh = Util.null2String(rs.getString("gh"));
                log.info("员工编号："+strYgbh);
                if (strGh.equals(strYgbh)) {
                    //职务
                    String strZw = Util.null2String(rs.getString("zw"));
                    //获取CRM评分
                    Double crm5 =  Util.getDoubleValue(rs.getString("crm5"),0.00);
                    //获取零售运营评分
                    Double yybz35 = Util.getDoubleValue(rs.getString("yybz35"),0.00);
                    //获取服务标准评分
                    Double fwbz40 = Util.getDoubleValue(rs.getString("fwbz40"),0.00);
                    //获取陈列管理
                    Double clbz20 = Util.getDoubleValue(rs.getString("clbz20"),0.00);
                    //总得分
                    Double db_zdf = Util.getDoubleValue(rs.getString("zdf"),0.00);
                    log.info("总得分："+db_zdf);
                    //评分日期
                    String khrq = Util.null2String(rs.getString("khrq"));
                    //考核周期
                    String khzq = Util.null2String(rs.getString("khzq"));
                    //个人奖金基数
                    str_jjjs_gr = Util.null2String(rs.getString("jjjs_gr"));
                    log.info("个人奖金基数：" + str_jjjs_gr);
                    //分公司奖金基数
                    str_jjjs_gs = Util.null2String(rs.getString("jjjs_gs"));
                    log.info("分公司奖金基数：" + str_jjjs_gs);
                    if(str_jjjs_gr==""){
                        str_jjjs = str_jjjs_gs;
                    }else {
                        str_jjjs = str_jjjs_gr;
                    }
                    log.info("奖金基数："+str_jjjs);
                    try {
                    //请假天数
                    str_qjDays = Util.getDoubleValue(rs.getString("qjts"), 0.00);
                    log.info("请假天数："+str_qjDays);
                    if (str_jjjs != null && !"".equals(str_jjjs) && db_zdf != 0.00) {
                        db_jjje = (db_zdf / 100) * Util.getDoubleValue(str_jjjs, 0.00);
                        //根据请假天数调整奖金金额
                        db_jjje = doubleFormat(commonsUtil.convertAmount(db_jjje, str_qjDays));
                    }
                        SyncUtil syncUtil = new SyncUtil();
                        String strFormid = syncUtil.getModeIdBytableName("uf_zddpgrjj_qyjl");
                        String strInsertData = "insert into uf_zddpgrjj_qyjl " +
                                "(fgs,xm,gh,zw,fwbz40,clbz20,crm5,yybz35,zdf,khzq,khrq,formmodeid,modedatacreater,modedatacreatertype,modedatacreatedate,modedatacreatetime,tsjg,qjts,jjje)" +
                                "values(" + strfgs + "," + strxm + ",'" + strYgbh + "'," + strZw + "," + fwbz40 + "," + clbz20 + " ," + crm5 + "," + yybz35 + "," + db_zdf + ",'" + khzq + "','" + khrq + "',"+strFormid+",1,0,to_char(SYSDATE,'yyyy-mm-dd'),to_char(SYSDATE,'hh24:mi:ss'),0," + str_qjDays + "," + db_jjje + ")";
                        log.info("终端店铺个人统计-区域经理插入数据sql：" + strInsertData);
                        Boolean insertResult = rsinsert.executeUpdate(strInsertData);
                        if (insertResult) {
                            log.info("数据插入到个人统计表成功！");
                        }else {
                            log.info("数据插入到个人统计表失败！");
                        }
                    }catch (Exception ex){
                        log.info("插入数据异常："+ex.toString());
                    }

                }
            }

        } catch (Exception ex) {
            log.info("数据插入到个人统计表发生异常：" + ex.toString());
        }
    }


    public Boolean UpdateData(String strTable, String strKhzq, String strRequestid, String strGh,Logger log) {
        Boolean result=true;
        RecordSet rs_update=new RecordSet();
        String str_jjjs = null;
        String str_jjjs_gr = null;
        String str_jjjs_gs = null;
        Double db_jjje = 0.00;
        Double str_qjDays = 0.00;
        log.info("根据员工编号与requestid查询数据......");
        RecordSet rs = getDataBygh(strTable, strRequestid,strGh,log);
        while (rs.next()) {
            String strYgbh = Util.null2String(rs.getString("gh"));
            if (strYgbh.equals(strGh)) {
                //获取分公司
                String strfgs = Util.null2String(rs.getString("fgs"));
                //获取员工id
                String strxm = Util.null2String(rs.getString("xm"));
                //职务
                String strZw = Util.null2String(rs.getString("zw"));
                //获取CRM评分
                Double crm5 = Util.getDoubleValue(rs.getString("crm5"),0.00);
                //获取零售运营评分
                Double yybz35 = Util.getDoubleValue(rs.getString("yybz35"),0.00);
                //获取服务标准评分
                Double fwbz40 = Util.getDoubleValue(rs.getString("fwbz40"),0.00);
                //获取陈列管理
                Double clbz20 = Util.getDoubleValue(rs.getString("clbz20"),0.00);
                //总得分
                Double db_zdf = Util.getDoubleValue(rs.getString("zdf"),0.00);
                //评分日期
                String khrq = Util.null2String(rs.getString("khrq"));
                //考核周期
                String khzq = Util.null2String(rs.getString("khzq"));
                //个人奖金基数
                str_jjjs_gr = Util.null2String(rs.getString("jjjs_gr"));
                //分公司奖金基数
                str_jjjs_gs = Util.null2String(rs.getString("jjjs_gs"));
                if (!"".equals(str_jjjs_gr) && str_jjjs_gr != null) {
                    str_jjjs = str_jjjs_gr;
                } else {
                    str_jjjs = str_jjjs_gs;
                }
                //请假天数
                str_qjDays = Util.getDoubleValue(rs.getString("qjts"), 0.00);
                if (str_jjjs != null && !"".equals(str_jjjs) && db_zdf != 0.00) {
                    db_jjje = (db_zdf / 100) * Util.getDoubleValue(str_jjjs, 0.00);
                    //根据请假天数调整奖金金额
                    db_jjje =doubleFormat(commonsUtil.convertAmount(db_jjje, str_qjDays));
                }
//                rsTrans = new RecordSetTrans();
                try {
//                    rsTrans.setAutoCommit(false);
                    String updateData = "UPDATE uf_zddpgrjj_qyjl SET " +
                            "crm5=?,yybz35=?,zdf=?,fwbz40=?,clbz20=?,qjts=?,jjje=?,tsjg=? " +
                            " WHERE khzq=? and gh=?";
                    log.info("执行更新sql:" + updateData);
                    rs_update.executeUpdate(updateData,crm5,yybz35,db_zdf,fwbz40,clbz20,str_qjDays,db_jjje,0,strKhzq,strGh);
//                    rsTrans.executeQuery(updateData,crm5,yybz35,db_zdf,fwbz40,clbz20,str_qjDays,db_jjje,0,strKhzq,strGh);
//                    rsTrans.commit();
                    result=true;

                }catch (Exception ex){
                    result=false;
//                    rsTrans.rollback();
                    log.info("更新数据异常："+ex.toString());
                }

            }
        }
        return result;
    }

    public RecordSet getAllData(String strTable, String strRequestid,Logger log) {
        RecordSet rs = new RecordSet();
        //查询表单数据
        String strData = "SELECT a.khzq,b.xm,b.gh,b.fgs,b.zw,b.yybz35,b.crm5,b.fwbz40,b.clbz20,b.zdf,b.khrq," +
                "c.lastname, c.jobtitle,d.jobtitlename,d.ecology_pinyin_search AS zwpy,e.jjjs AS jjjs_gr," +
                " f.qyjljs  AS jjjs_gs,NVL(qj.qjts, 0 ) AS qjts FROM " + strTable +
                " a INNER JOIN " + strTable + "_dt1 b ON a.id=b.mainid " +
                "INNER JOIN hrmresource c ON b.xm = c.id " +
                "INNER JOIN hrmjobtitles d ON b.zw = d.id " +
                "LEFT JOIN uf_jjjspzb_gr e ON b.xm = e.ygxm " +
                "LEFT JOIN uf_jjjspzb f ON b.fgs = f.fgs " +
                "LEFT JOIN w_all_leavelist qj ON SUBSTR( a.khzq, 1, 4 ) = qj.year AND SUBSTR( a.khzq, 5, 2 ) = qj.month AND b.xm =qj.resourceid"
                + " where a.requestid=" + strRequestid + "";
        log.info("执行sql查询所有数据：" + strData);
        rs.executeQuery(strData);
        log.info("受影响行数：" + rs.getCounts());
        return rs;
    }
    public RecordSet getDataBygh(String strTable, String strRequestid,String strGh,Logger log) {
        RecordSet rs = new RecordSet();
        //查询表单数据
        String strData = "SELECT a.khzq,b.xm,b.gh,b.fgs,b.zw,b.crm5,b.fwbz40,b.clbz20,b.zdf,b.khrq," +
                "c.lastname, c.jobtitle,d.jobtitlename,d.ecology_pinyin_search AS zwpy,e.jjjs AS jjjs_gr," +
                "CASE d.jobtitlename WHEN '店长' THEN f.dcjs  WHEN '副店长' THEN f.dcjs  WHEN '销售顾问' THEN f.dyjs  WHEN '区域经理' " +
                "THEN f.qyjljs ELSE f.mdzg  END AS jjjs_gs,NVL(qj.qjts, 0 ) AS qjts FROM " + strTable +
                " a INNER JOIN " + strTable + "_dt1 b ON a.id=b.mainid " +
                "INNER JOIN hrmresource c ON b.xm = c.id " +
                "INNER JOIN hrmjobtitles d ON b.zw = d.id " +
                "LEFT JOIN uf_jjjspzb_gr e ON b.xm = e.ygxm " +
                "LEFT JOIN uf_jjjspzb f ON b.fgs = f.fgs " +
                "LEFT JOIN w_all_leavelist qj ON SUBSTR( a.khzq, 1, 4 ) = qj.year AND SUBSTR( a.khzq, 5, 2 ) = qj.month AND b.xm =qj.resourceid"
                + " where a.requestid=" + strRequestid + " and b.gh='"+strGh+"'";
        log.info("执行sql查询所有数据：" + strData);
        rs.executeQuery(strData);
        log.info("根据员工编号查询受影响行数：" + rs.getCounts());
        return rs;
    }
}
